/* 
 * Enterprise Library Extensions - DI + ORM.
 * Copyright (C) 2006
 *
 * GNU General Public License
 *
 * This program is free software; you can redistribute 
 * it and/or modify it under the terms of the GNU General Public License 
 * as published by the Free Software Foundation; either version 2 of the 
 * License, or (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful, but 
 * WITHOUT ANY WARRANTY; without even the implied warranty of 
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. 
 * See the GNU General Public License for more details.
 */
using System;
using System.Data;
using System.Data.Common;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using Microsoft.Practices.EnterpriseLibrary.Data;
using ELE.EnterpriseLibrary.Properties;
using System.Configuration;

namespace ELE.EnterpriseLibrary.Data
{
  /// <summary>
  /// Base class with most of the functionality for a runner.
  /// </summary>
  public abstract class ObjectQueryRunnerBase : IObjectQueryRunner
  {
    #region Helper methods

    /// <summary>
    /// Overwrites a given mapping reference from a query or stored procedure with data from the mapping section
    /// </summary>
    /// <param name="objectMapping">object mapping from query or stored procedure</param>
    /// <param name="map">Mapping from mappings section</param>
    protected void OverwriteMapping(ObjectSqlMapping objectMapping, ObjectSqlMapping map)
    {
      if (objectMapping != null)
      {
        //OVERWRITE TABLE BINDINGS HERE
        if (map.Tables != null)
        {
          ObjectSqlMappingTable table = map.GetPrimaryTable();
          if (table.Bindings != null)
          {
            foreach (ObjectPropertySqlBinding binding in table.Bindings)
            {
              ObjectPropertySqlBinding objectBinding = objectMapping.GetBinding(binding.Name);
              if (objectBinding != null)
              {
                objectBinding.Ignore = binding.Ignore;
                if (!string.IsNullOrEmpty(binding.OutputFieldName))
                {
                  objectBinding.OutputFieldName = binding.OutputFieldName;
                }
                if (!string.IsNullOrEmpty(binding.InputFieldName))
                {
                  objectBinding.InputFieldName = binding.InputFieldName;
                }
                if (!string.IsNullOrEmpty(binding.NullableValue))
                {
                  objectBinding.NullableValue = binding.NullableValue;
                }
                if (!string.IsNullOrEmpty(binding.Direction))
                {
                  objectBinding.Direction = binding.Direction;
                }
              }
              else
              {
                throw new ConfigurationErrorsException(string.Format(Resources.Culture, Resources.ExceptionMappingReferenceIsInvalid, map.MapName, map.Name));
              }
            }
          }
        }
      }
      else
      {
        throw new ConfigurationErrorsException(string.Format(Resources.Culture, Resources.ExceptionMappingReferenceIsInvalid, map.MapName, map.Name));
      }
    }
    /// <summary>
    /// Creates and prepares a <see cref="DbCommand"/> from a given query definition and using the 
    /// value of input object to match to input parameters.
    /// </summary>
    /// <param name="database">Database defined by the Data Application Block to build the command against.</param>
    /// <param name="procedure">The query object.</param>
    /// <param name="inputObject">Input values.</param>
    /// <returns>The command.</returns>
    protected DbCommand PrepareStoredProcedureCommandInputs(Database database, ObjectStoredProcedure procedure, object inputObject)
    {
      DbCommand command = database.GetStoredProcCommand(procedure.Name);
      if (inputObject != null &&
          procedure.Mappings != null)
      {

        foreach (ObjectSqlMapping map in procedure.Mappings)
        {
          if (map.ObjectType == inputObject.GetType().FullName)
          {
            foreach (ObjectPropertySqlBinding binding in map.GetBindings())
            {
              if(!string.IsNullOrEmpty( binding.Direction) && 
                  (binding.Direction.ToLower().Equals("input") || binding.Direction.ToLower().Equals("in")))
              {
                this.SetCommandInputsFromBinding(database,
                                                 command,
                                                 map,
                                                 binding,
                                                 inputObject,
                                                 null);
              }
            }
          }

        }
      }
      return command;
    }
    /// <summary>
    /// Sets the inputs in a command from a bound property of an object
    /// </summary>
    /// <param name="database"></param>
    /// <param name="command"></param>
    /// <param name="objectMapping"></param>
    /// <param name="inputObject"></param>
    /// <param name="jointObject"></param>
    /// <param name="bind"></param>
    protected void SetCommandInputsFromBinding(Database database,
                                                DbCommand command,
                                                ObjectSqlMapping objectMapping,
                                                ObjectPropertySqlBinding bind,
                                                object inputObject,
                                                object jointObject)
    {
      object propVal = null;
      PropertyInfo propInfo = null;
      object inObj = null;
      if (string.IsNullOrEmpty(bind.JointColumn))
      {
        propInfo = inputObject.GetType().GetProperty(bind.PropertyName);
        inObj = inputObject;
      }
      else if (jointObject != null)
      {
        propInfo = jointObject.GetType().GetProperty(bind.PropertyName);
        inObj = jointObject;
      }
      else if (!bind.IsCollection)
      {
        ObjectSqlMapping subQuery = bind.SubQuery;
        string tabName = string.IsNullOrEmpty(bind.JointTable) ? null : bind.JointTable;
        ObjectSqlMappingTable tab = subQuery.GetTable(tabName);
        propInfo = inputObject.GetType().GetProperty(bind.PropertyName);
        inObj = propInfo.GetValue(inputObject, null);
        propInfo = inObj.GetType().GetProperty(tab.PrimaryKeyBinding.PropertyName);
      }
      if (propInfo != null)
      {
        propVal = propInfo.GetValue(inObj, null);
        if (propVal == null ||
             (!string.IsNullOrEmpty(bind.NullableValue) &&
             propVal.Equals(objectMapping.QueryBuilder.SqlParser.GetNullableObjectValue(bind.NullableValue, bind.SqlType))))
        {
          propVal = System.DBNull.Value;
        }
        database.AddInParameter(command,
                                bind.InputFieldName,
                                objectMapping.QueryBuilder.SqlParser.GetDbTypeFromString(bind.SqlType),
                                propVal);
      }
    }
    /// <summary>
    /// Creates and prepares a <see cref="DbCommand"/> from a given query definition and using the 
    /// value of input object to match to input parameters.
    /// </summary>
    /// <param name="database">Database defined by the Data Application Block to build the command against.</param>
    /// <param name="query">The query object.</param>
    /// <param name="inputObject">Input values.</param>
    /// <returns>The command.</returns>
    protected DbCommand PrepareCommandInputs(Database database, ObjectSqlQuery query, object inputObject)
    {
      DbCommand command = database.GetSqlStringCommand(query.SqlText);
      if (inputObject != null &&
          query.Mappings != null &&
          query.QueryBuilder != null &&
          query.QueryBuilder.SqlParser != null)
      {

        List<string> inputNames = query.QueryBuilder.SqlParser.GetInputParameterNames(query.SqlText);
        if (inputNames != null && inputNames.Count > 0)
        {
          foreach (ObjectSqlMapping map in query.Mappings)
          {
            this.SetCommandInputsFromMapping(database,
                                               command,
                                               query.QueryBuilder.SqlParser,
                                               query.SqlText,
                                               map,
                                               inputObject,
                                               null);
            
          }
        }
      }
      return command;
    }
    /// <summary>
    /// Prepares the inputs on a command from the values in a given object.
    /// </summary>
    /// <param name="database">Database defined by the Data Application Block to build the command against.</param>
    /// <param name="objectMapping">The mapping to use for matching properties with input names in the query.</param>
    /// <param name="inputObject">The object that holds the input values in its properties</param>
    /// <returns>The command with the parameters set.</returns>
    protected DbCommand PrepareCommandMappingInputs(Database database, ObjectSqlMapping objectMapping, object inputObject)
    {
      return this.PrepareCommandMappingInputs(database, objectMapping, inputObject, null);
    }
    /// <summary>
    /// Prepares the inputs on a command from the values in a given object and resolve 
    /// references to joints by using the given jointObject.
    /// </summary>
    /// <param name="database"></param>
    /// <param name="objectMapping"></param>
    /// <param name="inputObject"></param>
    /// <param name="jointObject"></param>
    /// <returns></returns>
    protected DbCommand PrepareCommandMappingInputs(Database database, 
                                                    ObjectSqlMapping objectMapping, 
                                                    object inputObject, 
                                                    object jointObject)
    {
      DbCommand command = database.GetSqlStringCommand(objectMapping.QueryText);
      if (inputObject != null &&
          objectMapping != null &&
          objectMapping.QueryBuilder != null &&
          objectMapping.QueryBuilder.SqlParser != null)
      {
        this.SetCommandInputsFromMapping(database, 
                                         command, 
                                         objectMapping.QueryBuilder.SqlParser, 
                                         objectMapping.QueryText, 
                                         objectMapping, 
                                         inputObject,
                                         jointObject);
      }
      return command;
    }
    /// <summary>
    /// This method actually sets the values of the input parameters.
    /// </summary>
    /// <param name="database"></param>
    /// <param name="command"></param>
    /// <param name="sqlParser"></param>
    /// <param name="sqlText"></param>
    /// <param name="objectMapping"></param>
    /// <param name="inputObject"></param>
    /// <param name="jointObject"></param>
    protected void SetCommandInputsFromMapping( Database database, 
                                                DbCommand command, 
                                                ISqlParser sqlParser, 
                                                string sqlText, 
                                                ObjectSqlMapping objectMapping, 
                                                object inputObject, 
                                                object jointObject)
    {
      if (objectMapping.ObjectType.Equals(inputObject.GetType().FullName))
      {

        List<string> inputNames = sqlParser.GetInputParameterNames(sqlText);
        foreach (ObjectSqlMappingTable table in objectMapping.Tables)
        {
          foreach (ObjectPropertySqlBinding bind in table.Bindings)
          {
            if (inputNames.Contains(bind.InputFieldName))
            {
              object propVal = null;
              PropertyInfo propInfo = null;
              object inObj = null;
              if (string.IsNullOrEmpty(bind.JointColumn))
              {
                propInfo = inputObject.GetType().GetProperty(bind.PropertyName);
                inObj = inputObject;
              }
              else if(jointObject != null)
              {
                propInfo = jointObject.GetType().GetProperty(bind.PropertyName);
                inObj = jointObject;
              }
              else if (!bind.IsCollection)
              {
                ObjectSqlMapping subQuery = bind.SubQuery;
                string tabName = string.IsNullOrEmpty(bind.JointTable) ? null : bind.JointTable;
                ObjectSqlMappingTable tab = subQuery.GetTable(tabName);
                propInfo = inputObject.GetType().GetProperty(bind.PropertyName);
                inObj = propInfo.GetValue(inputObject, null);
                propInfo = inObj.GetType().GetProperty(tab.PrimaryKeyBinding.PropertyName);
              }
              if (propInfo != null)
              {
                propVal = propInfo.GetValue(inObj, null);
                if (propVal == null ||
                     (!string.IsNullOrEmpty(bind.NullableValue) && 
                     propVal.Equals(objectMapping.QueryBuilder.SqlParser.GetNullableObjectValue(bind.NullableValue, bind.SqlType))))
                {
                  propVal = System.DBNull.Value;
                }
                database.AddInParameter(command,
                                        bind.InputFieldName,
                                        sqlParser.GetDbTypeFromString(bind.SqlType),
                                        propVal);
              }
            }
          }
        }
      }
    }
    /// <summary>
    /// Resolves an object instance from the current position of the reader by using the given mapping definition.
    /// </summary>
    /// <param name="database"></param>
    /// <param name="reader"></param>
    /// <param name="mapping"></param>
    /// <param name="recursive"></param>
    /// <returns></returns>
    protected object GetObjectFromReaderPosition(Database database, IDataReader reader, ObjectSqlMapping mapping, bool recursive)
    {
      object val = null;
      Assembly assembly = Assembly.Load(mapping.ObjectAssembly);
      val = assembly.CreateInstance(mapping.ObjectType);
      if(val != null)
      {
        foreach (ObjectSqlMappingTable table in mapping.Tables)
        {
          foreach (ObjectPropertySqlBinding binding in table.Bindings)
          {
            string outName = binding.OutputFieldName;
            if(string.IsNullOrEmpty(outName) && binding.IsCollection)
            {
              outName = mapping.PrimaryKeyBinding.OutputFieldName; 
            }
            int fieldIndex = reader.GetOrdinal(outName);
            if (fieldIndex >= 0)
            {
              PropertyInfo property = val.GetType().GetProperty(binding.PropertyName);
              if (property != null)
              {
                object toSet = null;
                if (binding.SubQuery != null)
                {
                  if (recursive)
                  {
                    Type subType = Type.GetType(binding.SubQuery.ObjectType + ", " + binding.SubQuery.ObjectAssembly);
                    PropertyInfo subProp = subType.GetProperty(binding.SubQuery.PrimaryKeyBinding.PropertyName);
                    if (binding.IsCollection)
                    {

                      if (!((typeof(System.Collections.IList)).IsAssignableFrom(property.PropertyType)))
                      {
                        throw new ConfigurationErrorsException(string.Format(Resources.Culture, Resources.ExceptionCollectionPropertyIsInvalid, binding.Name, binding.SubQuery.MapName, binding.SubQuery.Name));
                      }

                      PropertyInfo fkPropertyInfo = subType.GetProperty(mapping.PrimaryKeyBinding.PropertyName);
                      object inputVal = mapping.QueryBuilder.CastObjectFromReader(reader, fieldIndex, mapping.PrimaryKeyBinding.SqlType, fkPropertyInfo.PropertyType);
                      IList fkValues = null;

                      if (string.IsNullOrEmpty(binding.JointBinding))
                      {
                        if (string.IsNullOrEmpty(binding.JointColumn))
                        {
                          throw new ConfigurationErrorsException(string.Format(Resources.Culture, Resources.ExceptionForeignKeyPropertyNotFound, binding.Name));
                        }
                        fkValues = this.ExecuteObjectMappingQueryByForeignKey(database, binding.SubQuery, mapping, binding.PropertyName, inputVal, recursive);

                      }
                      else
                      {

                        ObjectPropertySqlBinding fkBinding = binding.SubQuery.GetBinding(binding.JointBinding);
                        if (fkBinding == null)
                        {
                          throw new ConfigurationErrorsException(string.Format(Resources.Culture, Resources.ExceptionBindingPropertyNotFound, binding.JointBinding, binding.SubQuery.MapName, binding.SubQuery.Name));
                        }
                        fkValues = this.ExecuteObjectMappingQueryByProperty(database, binding.SubQuery, fkBinding.Name, inputVal, recursive);

                      }
                      if (fkValues != null)
                      {
                        IList valColl = (IList)Activator.CreateInstance(property.PropertyType);
                        foreach (object fkVal in fkValues)
                        {
                          valColl.Add(fkVal);
                        }
                        toSet = valColl;
                      }
                    }
                    else
                    {
                      object inputVal = mapping.QueryBuilder.CastObjectFromReader(reader, fieldIndex, binding.SubQuery.PrimaryKeyBinding.SqlType, subProp.PropertyType);
                      toSet = this.QueryObjectByPrimaryKey(database, binding.SubQuery.MapName, binding.SubQuery.Name, inputVal);
                    }
                  }
                }
                else
                {
                  toSet = mapping.QueryBuilder.CastObjectFromReader(reader, fieldIndex, binding.SqlType, property.PropertyType);
                }
                if (!string.IsNullOrEmpty(binding.NullableValue) && !string.IsNullOrEmpty(binding.SqlType))
                {
                  object nullableObject = mapping.QueryBuilder.SqlParser.GetNullableObjectValue(binding.NullableValue, binding.SqlType);
                  if (toSet == null || toSet == DBNull.Value)
                  {
                    toSet = nullableObject;
                  }
                }
                property.SetValue(val, toSet, null);
                
              }
              else
              {
                throw new ConfigurationErrorsException(string.Format(Resources.Culture, Resources.ExceptionBindingPropertyNotFound, binding.PropertyName, mapping.MapName, mapping.Name));
              }
            }
            else
            {
              throw new InvalidObjectQueryMappingException(string.Format(Resources.Culture, Resources.ExceptionInvalidObjectMapping, "outName=" + binding.OutputFieldName + " of binding '" + binding.Name + "' is not in the resultset", mapping.MapName, mapping.Name));
            }
          }
        }
      }
      return val;
    }
    #endregion
    #region General helper methods
    /// <summary>
    /// Gets an object instance from a mapping instance by using <see cref="ObjectSqlMapping.ObjectAssembly"/> and
    /// <see cref="ObjectSqlMapping.ObjectType"/>
    /// </summary>
    /// <param name="mapping"></param>
    /// <returns></returns>
    public object GetMappingObjectInstance(ObjectSqlMapping mapping)
    {
      Assembly oAssembly = Assembly.Load(mapping.ObjectAssembly);
      return oAssembly.CreateInstance(mapping.ObjectType);
    }
    #endregion

    #region Select query helper methods

    /// <summary>
    /// Helper method to execute a data reader query.
    /// </summary>
    /// <param name="database"></param>
    /// <param name="query"></param>
    /// <param name="inputObject"></param>
    /// <returns></returns>
    protected IDataReader ExecuteDatareaderQueryByNameHelper(Database database, ObjectSqlQuery query, object inputObject)
    {
      using (DbCommand command = this.PrepareCommandInputs(database, query, inputObject))
      {
        IDataReader reader = null;

        reader = database.ExecuteReader(command);

        // Note: connection was closed by ExecuteDataSet method call 

        return reader;
      }
    }
    /// <summary>
    /// Helper method to execute a query by primary key.
    /// </summary>
    /// <param name="database"></param>
    /// <param name="mappingId"></param>
    /// <param name="objectId"></param>
    /// <param name="pkValue"></param>
    /// <param name="recursive"></param>
    /// <returns></returns>
    protected object QueryObjectByPrimaryKeyHelper(Database database, string mappingId, string objectId, object pkValue, bool recursive)
    {
      ObjectSqlMapping mapping = (ObjectSqlMapping)this.GetMapping(mappingId, objectId).Clone();
      Assembly oAssembly = Assembly.Load(mapping.ObjectAssembly);
      object inputObject = oAssembly.CreateInstance(mapping.ObjectType);
      PropertyInfo pkProperty = inputObject.GetType().GetProperty(mapping.PrimaryKeyBinding.PropertyName);
      if (pkProperty != null)
      {
        pkProperty.SetValue(inputObject, pkValue, null);
      }
      else
      {
        //TODO: throw exception due to misconfiguration
      }
      mapping.BuildQueryText(MappingQueryType.Select);
      using (DbCommand command = this.PrepareCommandMappingInputs(database, mapping, inputObject))
      {
        using (IDataReader reader = database.ExecuteReader(command))
        {
          // Note: connection was closed by ExecuteDataSet method call 

          if (reader.Read())
          {
            return this.GetObjectFromReaderPosition(database, reader, mapping, recursive);
          }
        }

      }
      return null;

    }
    /// <summary>
    /// Helper method to execute query.
    /// </summary>
    /// <param name="database"></param>
    /// <param name="mapping"></param>
    /// <param name="propertyName"></param>
    /// <param name="inputValue"></param>
    /// <param name="recursive"></param>
    /// <returns></returns>
    protected IList ExecuteObjectMappingQueryByProperty(Database database, ObjectSqlMapping mapping, string propertyName, object inputValue, bool recursive)
    {
      object inputObject = this.GetMappingObjectInstance(mapping);
      PropertyInfo inputProperty = inputObject.GetType().GetProperty(propertyName);
      if (inputProperty != null)
      {
        inputProperty.SetValue(inputObject, inputValue, null);
      }
      else
      {
        throw new ConfigurationErrorsException(string.Format(Resources.Culture, Resources.ExceptionBindingPropertyNotFound, propertyName, mapping.MapName, mapping.Name));
      }
      IList results = null;
      mapping.BuildQueryByPropertyBindingText(propertyName);
      using (DbCommand command = this.PrepareCommandMappingInputs(database, mapping, inputObject))
      {
        using (IDataReader reader = database.ExecuteReader(command))
        {
          results = new ArrayList();
          while (reader.Read())
          {
            results.Add(this.GetObjectFromReaderPosition(database, reader, mapping, recursive));
          }
        }

      }
      return results;

    }
    /// <summary>
    /// Executes a query by foreign key.
    /// </summary>
    /// <param name="database"></param>
    /// <param name="mapping"></param>
    /// <param name="fkMapping"></param>
    /// <param name="propertyName"></param>
    /// <param name="inputValue"></param>
    /// <param name="recursive"></param>
    /// <returns></returns>
    public IList ExecuteObjectMappingQueryByForeignKey(
              Database database,
              ObjectSqlMapping mapping,
              ObjectSqlMapping fkMapping,
              string propertyName,
              object inputValue,
              bool recursive)
    {
      object inputObject = this.GetMappingObjectInstance(fkMapping);
      PropertyInfo inputProperty = inputObject.GetType().GetProperty(fkMapping.PrimaryKeyBinding.PropertyName);
      if (inputProperty != null)
      {
        inputProperty.SetValue(inputObject, inputValue, null);
      }
      else
      {
        throw new ConfigurationErrorsException(string.Format(Resources.Culture, Resources.ExceptionBindingPropertyNotFound, propertyName, fkMapping.MapName, fkMapping.Name));
      }
      IList results = null;
      fkMapping.BuildQueryByPropertyBindingText(propertyName);
      using (DbCommand command = this.PrepareCommandMappingInputs(database, fkMapping, inputObject))
      {
        using (IDataReader reader = database.ExecuteReader(command))
        {
          results = new ArrayList();
          while (reader.Read())
          {
            results.Add(this.GetObjectFromReaderPosition(database, reader, mapping, recursive));
          }
        }

      }
      return results;

    }
    #endregion

    #region Update helper methods
    /// <summary>
    /// Runs a query by primary key to determine whether an insert or an update query are needed.
    /// </summary>
    /// <param name="database"></param>
    /// <param name="mapping"></param>
    /// <param name="inputObject"></param>
    /// <returns></returns>
    protected MappingQueryType ResolveMappingUpdateType(Database database, ObjectSqlMapping mapping, object inputObject)
    {
      PropertyInfo pkProperty = inputObject.GetType().GetProperty(mapping.PrimaryKeyBinding.PropertyName);
      object pkObject = pkProperty.GetValue(inputObject, null);
      object pkLookupResult = this.QueryObjectByPrimaryKeyLightweight(database, mapping.MapName, mapping.Name, pkObject);
      if (pkLookupResult != null)
      {
        return MappingQueryType.Update;
      }
      else
      {
        return MappingQueryType.Insert;
      }
    }
    /// <summary>
    /// Returns true if the binding is a collection and the update type is not delete.
    /// </summary>
    /// <param name="binding"></param>
    /// <param name="updateType"></param>
    /// <returns></returns>
    protected bool GetCollectionDependencyRequirement(ObjectPropertySqlBinding binding, MappingQueryType updateType)
    {
      bool preDep = !binding.IsCollection;
      if (updateType == MappingQueryType.Delete)
      {
        return !preDep;
      }
      return preDep;
    }
    /// <summary>
    /// Executes an update statement.
    /// <para>This method uses a transaction.</para>
    /// </summary>
    /// <param name="database"></param>
    /// <param name="mappingId"></param>
    /// <param name="objectId"></param>
    /// <param name="objectToPersist"></param>
    /// <param name="persistenceType"></param>
    /// <returns></returns>
    protected int ExecutePersistenceUpdate(Database database,
                                            string mappingId,
                                            string objectId,
                                            object objectToPersist,
                                            MappingQueryType persistenceType)
    {
      ObjectSqlMapping mapping = (ObjectSqlMapping)this.GetMapping(mappingId, objectId).Clone();
      int rowCount = 0;

      using (DbConnection connection = database.CreateConnection())
      {
        connection.Open();
        DbTransaction transaction = connection.BeginTransaction();

        try
        {
          rowCount = this.ExecuteTransactionalUpdate(database, transaction, mapping, objectToPersist, null, persistenceType);
          // Commit the transaction.
          transaction.Commit();
        }
        catch (Exception e)
        {
          // Roll back the transaction. 
          transaction.Rollback();
          throw e;
        }
        finally
        {
          connection.Close();
        }

        return rowCount;
      }
    }
    /// <summary>
    /// The name says it all.
    /// </summary>
    /// <param name="database"></param>
    /// <param name="transaction"></param>
    /// <param name="mapping"></param>
    /// <param name="table"></param>
    /// <param name="objectToPersist"></param>
    /// <param name="jointObject"></param>
    /// <param name="persistenceType"></param>
    /// <returns></returns>
    protected int ProcessTableForUpdates(Database database,
                                          DbTransaction transaction,
                                          ObjectSqlMapping mapping,
                                          ObjectSqlMappingTable table,
                                          object objectToPersist,
                                          object jointObject,
                                          MappingQueryType persistenceType)
    {
      int rowCount = 0;
      if (objectToPersist != null)
      {
        foreach (ObjectPropertySqlBinding binding in table.ReferencedBindings)
        {
          if (this.GetCollectionDependencyRequirement(binding, persistenceType))
          {
            rowCount += this.ExecuteReferencedUpdate(database, transaction, mapping, binding, objectToPersist, persistenceType);
          }
        }
        mapping.BuildQueryText(persistenceType, table.Name);
        using (DbCommand command = this.PrepareCommandMappingInputs(database, mapping, objectToPersist, jointObject))
        {
          rowCount += database.ExecuteNonQuery(command, transaction);
        }
        if (rowCount > 0)
        {
          foreach (ObjectPropertySqlBinding binding in table.ReferencedBindings)
          {
            if (!this.GetCollectionDependencyRequirement(binding, persistenceType))
            {
              rowCount += this.ExecuteReferencedUpdate(database, transaction, mapping, binding, objectToPersist, persistenceType);
            }
          }
        }
      }
      return rowCount;
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="database"></param>
    /// <param name="transaction"></param>
    /// <param name="mapping"></param>
    /// <param name="inputObject"></param>
    /// <param name="jointObject"></param>
    /// <param name="updateType"></param>
    /// <returns></returns>
    protected int ExecuteTransactionalUpdate(Database database,
                                              DbTransaction transaction,
                                              ObjectSqlMapping mapping,
                                              object inputObject,
                                              object jointObject,
                                              MappingQueryType updateType)
    {
      int rowCount = 0;
      //On updates (Insert or Update), process the primary table first so that fk constraints are not violated on the rest of the tables
      if (updateType != MappingQueryType.Delete)
      {
        updateType = this.ResolveMappingUpdateType(database, mapping, inputObject);
        rowCount += this.ProcessTableForUpdates(database, transaction, mapping, mapping.GetPrimaryTable(), inputObject, jointObject, updateType);
      }
      foreach (ObjectSqlMappingTable table in mapping.Tables)
      {
        if (table.Name != mapping.PrimaryTableName)
        {
          rowCount += this.ProcessTableForUpdates(database, transaction, mapping, table, inputObject, jointObject, updateType);
        }
      }
      //On delete, process the primary table last so that fk constraints are not violated on the rest of the tables
      if (updateType == MappingQueryType.Delete)
      {
        rowCount += this.ProcessTableForUpdates(database, transaction, mapping, mapping.GetPrimaryTable(), inputObject, jointObject, updateType);
      }
      return rowCount;
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="database"></param>
    /// <param name="transaction"></param>
    /// <param name="mapping"></param>
    /// <param name="binding"></param>
    /// <param name="objectToPersist"></param>
    /// <param name="updateType"></param>
    /// <returns></returns>
    protected int ExecuteReferencedUpdate(Database database,
                                          DbTransaction transaction,
                                          ObjectSqlMapping mapping,
                                          ObjectPropertySqlBinding binding,
                                          object objectToPersist,
                                          MappingQueryType updateType)
    {
      object boundObject = null;
      int rowCount = 0;
      PropertyInfo boundObjectProperty = objectToPersist.GetType().GetProperty(binding.PropertyName);
      if (boundObjectProperty != null)
      {
        boundObject = boundObjectProperty.GetValue(objectToPersist, null);
        if (boundObject != null)
        {
          ObjectSqlMapping subQuery = (ObjectSqlMapping)binding.SubQuery.Clone();
          if ((typeof(IList)).IsAssignableFrom(boundObject.GetType()))
          {
            if (!binding.IsCollection)
            {
              throw new ConfigurationErrorsException(string.Format(Resources.Culture, Resources.ExceptionCollectionPropertyUndefined, binding.PropertyName, binding.Name, mapping.MapName, mapping.Name));
            }
            if (string.IsNullOrEmpty(binding.JointBinding))
            {
              ObjectPropertySqlBinding jointBinding = (ObjectPropertySqlBinding)mapping.GetPrimaryTable().PrimaryKeyBinding.Clone();
              jointBinding.SubQuery = null;
              jointBinding.ColumnName = binding.JointColumn;
              jointBinding.JointColumn = binding.JointColumn;
              jointBinding.JointTable = binding.JointTable;
              jointBinding.InputFieldName = binding.InputFieldName;
              jointBinding.Name = mapping.Name + jointBinding.Name;
              if (string.IsNullOrEmpty(binding.JointTable))
              {
                subQuery.GetPrimaryTable().AddBinding(jointBinding);
              }
              else
              {
                subQuery.GetTable(binding.JointTable).AddBinding(jointBinding);
              }
            }
            IList boundObjectList = (IList)boundObject;
            foreach (object bo in boundObjectList)
            {
              rowCount += this.ExecuteTransactionalUpdate(database,
                                                          transaction,
                                                          subQuery,
                                                          bo,
                                                          objectToPersist,
                                                          updateType);
            }
          }
          else
          {
            rowCount += this.ExecuteTransactionalUpdate(database,
                                                        transaction,
                                                        subQuery,
                                                        boundObject,
                                                        null,
                                                        updateType);
          }
        }
      }
      return rowCount;
    }
    #endregion
    #region Abstract method to be implemented
    /// <summary>
    /// Get a mapping definition by map id and object id.
    /// </summary>
    /// <param name="mappingId"></param>
    /// <param name="objectId"></param>
    /// <returns></returns>
    public abstract ObjectSqlMapping GetMapping(string mappingId, string objectId);
    /// <summary>
    /// Helper method to execute a query by name
    /// </summary>
    /// <param name="database"></param>
    /// <param name="queryId"></param>
    /// <param name="inputObject"></param>
    /// <param name="recursive"></param>
    /// <returns></returns>
    protected abstract IList ExecuteQueryByNameHelper(Database database, string queryId, object inputObject, bool recursive);
    /// <summary>
    /// Helper method to execute a query by name
    /// </summary>
    /// <param name="database"></param>
    /// <param name="storedProcedureId"></param>
    /// <param name="inputObject"></param>
    /// <param name="recursive"></param>
    /// <returns></returns>
    protected abstract IList ExecuteStoredProcedureByNameHelper(Database database, string storedProcedureId, object inputObject, bool recursive);

    #endregion 
    #region IObjectQueryRunner Members

    /// <summary>
    /// Subclasses must implement this interface method.
    /// See <see cref="IObjectQueryRunner.ExecuteDatasetStoredProcedureByName(Database, string, object)"/> for more information.
    /// </summary>
    /// <param name="database"></param>
    /// <param name="storedProcedureId"></param>
    /// <param name="inputObject"></param>
    /// <returns></returns>
    public abstract DataSet ExecuteDatasetStoredProcedureByName(Database database, string storedProcedureId, object inputObject);
    /// <summary>
    /// Implementation of the interface method.
    /// See <see cref="IObjectQueryRunner.ExecuteQueryByName(Database, string, object)"/> for more information.
    /// </summary>
    /// <param name="database"></param>
    /// <param name="storedProcedureId"></param>
    /// <param name="inputObject"></param>
    /// <returns></returns>
    public IList ExecuteStoredProcedureByName(Database database, string storedProcedureId, object inputObject)
    {
      return this.ExecuteStoredProcedureByNameHelper(database, storedProcedureId, inputObject, true);
    }
    /// <summary>
    /// Subclasses must implement this interface method.
    /// See <see cref="IObjectQueryRunner.ExecuteDatasetQueryByName(Database, string, object)"/> for more information.
    /// </summary>
    /// <param name="database"></param>
    /// <param name="queryId"></param>
    /// <param name="inputObject"></param>
    /// <returns></returns>
    public abstract DataSet ExecuteDatasetQueryByName(Database database, string queryId, object inputObject);
    /// <summary>
    /// Subclasses must implement this interface method.
    /// See <see cref="IObjectQueryRunner.ExecuteDatareaderQueryByName(Database, string, object)"/> for more information.
    /// </summary>
    /// <param name="database"></param>
    /// <param name="queryId"></param>
    /// <param name="inputObject"></param>
    /// <returns></returns>
    public abstract IDataReader ExecuteDatareaderQueryByName(Database database, string queryId, object inputObject);
    /// <summary>
    /// Implementation of the interface method.
    /// See <see cref="IObjectQueryRunner.ExecuteQueryByName(Database, string, object)"/> for more information.
    /// </summary>
    /// <param name="database"></param>
    /// <param name="queryId"></param>
    /// <param name="inputObject"></param>
    /// <returns></returns>
    public IList ExecuteQueryByName(Database database, string queryId, object inputObject)
    {
      return this.ExecuteQueryByNameHelper(database, queryId, inputObject, true);
    }
    /// <summary>
    /// Implementation of the interface method.
    /// See <see cref="IObjectQueryRunner.ExecuteQueryByNameLightweight(Database, string, object)"/> for more information.
    /// </summary>
    /// <param name="database"></param>
    /// <param name="queryId"></param>
    /// <param name="inputObject"></param>
    /// <returns></returns>
    public IList ExecuteQueryByNameLightweight(Database database, string queryId, object inputObject)
    {
      return this.ExecuteQueryByNameHelper(database, queryId, inputObject, false);
    }
    /// <summary>
    /// Implementation of the interface method.
    /// See <see cref="IObjectQueryRunner.QueryObjectByPrimaryKey(Database, string, object)"/> for more information.
    /// </summary>
    /// <param name="database"></param>
    /// <param name="objectId"></param>
    /// <param name="pkValue"></param>
    /// <returns></returns>
    public object QueryObjectByPrimaryKey(Database database, string objectId, object pkValue)
    {
      return this.QueryObjectByPrimaryKey(database, null, objectId, pkValue);
    }
    /// <summary>
    /// Implementation of the interface method.
    /// See <see cref="IObjectQueryRunner.QueryObjectByPrimaryKey(Database, string, string, object)"/> for more information.
    /// </summary>
    /// <param name="database"></param>
    /// <param name="mappingId"></param>
    /// <param name="objectId"></param>
    /// <param name="pkValue"></param>
    /// <returns></returns>
    public object QueryObjectByPrimaryKey(Database database, string mappingId, string objectId, object pkValue)
    {
      return this.QueryObjectByPrimaryKeyHelper(database, mappingId, objectId, pkValue, true);
    }
    /// <summary>
    /// Implementation of the interface method.
    /// See <see cref="IObjectQueryRunner.QueryObjectByPrimaryKeyLightweight(Database, string, object)"/> for more information.
    /// </summary>
    /// <param name="database"></param>
    /// <param name="objectId"></param>
    /// <param name="pkValue"></param>
    /// <returns></returns>
    public object QueryObjectByPrimaryKeyLightweight(Database database, string objectId, object pkValue)
    {
      return this.QueryObjectByPrimaryKeyLightweight(database, null, objectId, pkValue);
    }
    /// <summary>
    /// Implementation of the interface method.
    /// See <see cref="IObjectQueryRunner.QueryObjectByPrimaryKeyLightweight(Database, string, string, object)"/> for more information.
    /// </summary>
    /// <param name="database"></param>
    /// <param name="mappingId"></param>
    /// <param name="objectId"></param>
    /// <param name="pkValue"></param>
    /// <returns></returns>
    public object QueryObjectByPrimaryKeyLightweight(Database database, string mappingId, string objectId, object pkValue)
    {
      return this.QueryObjectByPrimaryKeyHelper(database, mappingId, objectId, pkValue, false);
    }
    /// <summary>
    /// Implementation of the interface method.
    /// See <see cref="IObjectQueryRunner.QueryObjectByProperty(Database, string, string, object)"/> for more information.
    /// </summary>
    /// <param name="database"></param>
    /// <param name="objectId"></param>
    /// <param name="propertyName"></param>
    /// <param name="inputValue"></param>
    /// <returns></returns>
    public IList QueryObjectByProperty(Database database, string objectId, string propertyName, object inputValue)
    {
      return this.QueryObjectByProperty(database, null, objectId, propertyName, inputValue);
    }
    /// <summary>
    /// Implementation of the interface method.
    /// See <see cref="IObjectQueryRunner.QueryObjectByProperty(Database, string, string, string, object)"/> for more information.
    /// </summary>
    /// <param name="database"></param>
    /// <param name="mappingId"></param>
    /// <param name="objectId"></param>
    /// <param name="propertyName"></param>
    /// <param name="inputValue"></param>
    /// <returns></returns>
    public IList QueryObjectByProperty(Database database, string mappingId, string objectId, string propertyName, object inputValue)
    {
      ObjectSqlMapping mapping = (ObjectSqlMapping)this.GetMapping(mappingId, objectId).Clone();
      return this.ExecuteObjectMappingQueryByProperty(database, mapping, propertyName, inputValue, true);
    }
    /// <summary>
    /// Implementation of the interface method.
    /// See <see cref="IObjectQueryRunner.QueryObjectByPropertyLightweight(Database, string, string, object)"/> for more information.
    /// </summary>
    /// <param name="database"></param>
    /// <param name="objectId"></param>
    /// <param name="propertyName"></param>
    /// <param name="inputValue"></param>
    /// <returns></returns>
    public IList QueryObjectByPropertyLightweight(Database database, string objectId, string propertyName, object inputValue)
    {
      return this.QueryObjectByPropertyLightweight(database, null, objectId, propertyName, inputValue);
    }
    /// <summary>
    /// Implementation of the interface method.
    /// See <see cref="IObjectQueryRunner.QueryObjectByPropertyLightweight(Database, string, string, string, object)"/> for more information.
    /// </summary>
    /// <param name="database"></param>
    /// <param name="mappingId"></param>
    /// <param name="objectId"></param>
    /// <param name="propertyName"></param>
    /// <param name="inputValue"></param>
    /// <returns></returns>
    public IList QueryObjectByPropertyLightweight(Database database, string mappingId, string objectId, string propertyName, object inputValue)
    {
      ObjectSqlMapping mapping = (ObjectSqlMapping)this.GetMapping(mappingId, objectId).Clone();
      return this.ExecuteObjectMappingQueryByProperty(database, mapping, propertyName, inputValue, false);
    }
    /// <summary>
    /// Implementation of the interface method.
    /// See <see cref="IObjectQueryRunner.PersistObject(Database, string, object)"/> for more information.
    /// </summary>
    /// <param name="database"></param>
    /// <param name="objectId"></param>
    /// <param name="objectToPersist"></param>
    /// <returns></returns>
    public int PersistObject(Database database, string objectId, object objectToPersist)
    {
      return this.PersistObject(database, null, objectId, objectToPersist);
    }
    /// <summary>
    /// Implementation of the interface method.
    /// See <see cref="IObjectQueryRunner.PersistObject(Database, string, string, object)"/> for more information.
    /// </summary>
    /// <param name="database"></param>
    /// <param name="mappingId"></param>
    /// <param name="objectId"></param>
    /// <param name="objectToPersist"></param>
    /// <returns></returns>
    public int PersistObject(Database database, string mappingId, string objectId, object objectToPersist)
    {
      return this.ExecutePersistenceUpdate(database, mappingId, objectId, objectToPersist, MappingQueryType.Update);
    }
    /// <summary>
    /// Implementation of the interface method.
    /// See <see cref="IObjectQueryRunner.RemoveObject(Database, string, object)"/> for more information.
    /// </summary>
    /// <param name="database"></param>
    /// <param name="objectId"></param>
    /// <param name="objectToRemove"></param>
    /// <returns></returns>
    public int RemoveObject(Database database, string objectId, object objectToRemove)
    {
      return this.RemoveObject(database, null, objectId, objectToRemove);
    }
    /// <summary>
    /// Implementation of the interface method.
    /// See <see cref="IObjectQueryRunner.RemoveObject(Database, string, string, object)"/> for more information.
    /// </summary>
    /// <param name="database"></param>
    /// <param name="mappingId"></param>
    /// <param name="objectId"></param>
    /// <param name="objectToRemove"></param>
    /// <returns></returns>
    public int RemoveObject(Database database, string mappingId, string objectId, object objectToRemove)
    {
      return this.ExecutePersistenceUpdate(database, mappingId, objectId, objectToRemove, MappingQueryType.Delete);
    }
    #endregion
  }
}
